--此脚本在EAS库内执行

--清除测试用户数据
select * from firstdb.systemusers;
delete firstdb.systemusers where userid >1 ;

--清除测试用户组数据
select * from firstdb.systemgroups;
delete firstdb.systemgroups where groupid >2 ;

--清除测试用户组关联表数据
select * from firstdb.systemgroupmembership;
delete firstdb.systemgroupmembership where groupid >1 ;


--同步用户   
insert into firstdb.systemusers(username,fullname,easUserID,status,StyleID)
select FNumber,FName_L2,FID,1,1 
from T_PM_User where fid not in(select fid from T_PM_User 
where (lower(FNumber) like 'zt%' or lower(FName_L2) like 'zt%')
or lower(FNumber)='administrator' or lower(FName_L2)='administrator');
  
--同步用户组
insert into firstdb.systemgroups(groupname,easgroupid,isdept,leaderorder,easLongNumber,
       easNumber,easParentID,isManageDept,easDisplayName,parentid)
select FName_L2 as name,FID,1,99,flongnumber,FNumber as "number",FParentID as "parent",
       FIsCU, FDisplayName_L2 as DisplayName,0
from T_ORG_BASEUNIT;


-------------表情更新组织结构-----------
update firstdb.systemgroups 
set parentID = (select max(groupid) from firstdb.systemgroups b where b.easgroupID = firstdb.systemgroups.easParentID )
where isdept = 1 

--set the root org's parentid = 0
update firstdb.systemgroups 
set parentid = 0
where  parentid is null;

select * from firstdb.systemgroups where parentid is null;


--同步用户组关联表
insert into firstdb.systemgroupmembership(groupid,userid) 
	select fu.userid,fg.groupid
	 from firstdb.systemusers fu 
   left outer join T_PM_User usr on fu.easuserid=usr.fid
	left outer join 
		(select pmuser.fid,pmuser.ftype,pmuser.FNumber,pmuser.FName_L2,person.FGender,person.Fbirthday,person.FEmail,
		person.FAddress_L2,person.FHomePhone,person.FOfficePhone,person.FCell,e.fid as orgunitid,e.fname_l2 as orgname
		from t_pm_user pmuser
		left outer join t_bd_person person on pmuser.fpersonid = person.fid
		left outer join t_org_positionmember c on person.fid = c.fpersonid
		left outer join t_org_position d on d.fid = c.fpositionid
		left outer join t_org_baseunit e on e.fid = d.fadminorgunitid
		 where  c.fisPrimary = 1 ) 
     bs on usr.fid = bs.fid
   left outer join firstdb.systemgroups fg on fg.easgroupid=nvl(bs.orgunitid,usr.fdeforgunitid)
   where fu.userid>1



--增加组成员属性

insert into firstdb.systemgroupmembership(groupid,userid) values(1101,1);


insert into firstdb.systemgroupmembership(groupid,userid) 
select 2,userid from firstdb.systemusers where userid>1;




select * from firstdb.systemusers;
select * from firstdb.systemgroups;
select * from firstdb.systemgroupmembership;